﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Xml;
using System.IO;
using System.Data;

namespace ArticleManager.DataClasses
{
    class DatabaseValidator
    {
        OleDbConnection __Connection;
        TableDataCollection __tablesList;


        public DatabaseValidator(OleDbConnection Connection)
        {
            __tablesList = new TableDataCollection();
            __Connection = Connection;
            __tablesList.Clear();
        }

        private void InitTables()
        {
            string configpath = Directory.GetCurrentDirectory();
            if (configpath.Contains("Release") || configpath.Contains("Debug"))
            {
                string[] pathParts = configpath.Split(System.IO.Path.DirectorySeparatorChar);
                System.Diagnostics.Debug.Assert(pathParts.Length > 3);
                string pathSep = System.Char.ToString(System.IO.Path.DirectorySeparatorChar);
                if (pathParts.Length > 3)
                    configpath = System.String.Join(pathSep, pathParts, 0, pathParts.Length - 2);
            }
            configpath += @"\dbconfig.xml";

            FileStream fs = new FileStream(configpath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            XmlDocument config = new XmlDocument();
            config.Load(fs);
            System.Xml.XmlNodeList NodeList = config.GetElementsByTagName("table");
            foreach (XmlNode node in NodeList)
            {
                TableData table = new TableData();
                table.Name = node.Attributes["name"].Value;
                table.Query = node.Attributes["createsql"].Value;
                table.KeyQuery = node.Attributes["pksql"].Value;

                //Load the columns and sql.
                foreach(XmlNode nodecol in node.FirstChild.ChildNodes)
                {
                    ColumnData col = new ColumnData();
                    col.Name = nodecol.Attributes["name"].Value;
                    col.Type = nodecol.Attributes["type"].Value;
                    col.Table = table.Name;
                    table.Columns.Add(col); 
                }
                __tablesList.Add(table);
            }

        }

        private void CheckTables()
        {
            string tablename = "";
            string colname = "";
            //string keyname = "";
            int nTableIndex = -1;
            int nColIndex = -1;
            // We only want user tables, not system tables
            string[] restrictions = new string[4];
            restrictions[3] = "Table";
            DataTable dataTable = __Connection.GetSchema("Tables", restrictions);

            foreach (DataRow row in dataTable.Rows)
            {
                tablename = row["TABLE_NAME"] as string;
                nTableIndex = __tablesList[tablename];
                if (nTableIndex != -1)
                {
                    __tablesList[nTableIndex].Exist = true;

                    //Get the columns of the table.
                    DataTable tabCols = __Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, tablename, null });
                    foreach (DataRow col in tabCols.Rows)
                    {
                        colname = col[3] as string;
                        nColIndex = __tablesList[nTableIndex].Columns[colname];
                        if (nColIndex != -1)
                        {
                            __tablesList[nTableIndex].Columns[nColIndex].Exist = true;
                        }
                    }

                    //Get the primary key contraints.
                    DataTable tabKeys = __Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new Object[] { null, null, tablename });
                    foreach (DataRow key in tabKeys.Rows)
                    {
                        //keyname = key[1] as string;
                        //If only one row exists, that is enpugh to know that key exists.
                        __tablesList[nTableIndex].KeyExist = true;
                        break;
                    }
                }
            }


        }


        private void CreateTables()
        {
            foreach (TableData table in __tablesList)
            {
                //Create Table;
                if (!table.Exist)
                {
                    ExecuteQuery(table.Query);
                }

                //Create Columns.
                foreach (ColumnData cd in table.Columns)
                {
                    if (!cd.Exist)
                    {
                         ExecuteQuery(cd.Query);
                    }
                }

                //Create Contraints.
                if (!table.KeyExist)
                {
                    ExecuteQuery(table.KeyQuery);
                }
            }
        }



        private void ExecuteQuery(string query)
        {
            query = query.Trim();

            if (0 == query.Length)
            {
                return;
            }
            try
            {
                Controller.Instance.LogError("Executing query "+query);
                using (OleDbCommand cmd = new OleDbCommand(query, __Connection))
                {
                    int temp = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Controller.Instance.LogException(ex);
            }
            return;
        }

        public bool CheckDatabaseCompatibility()
        {
            InitTables();
            CheckTables();
            CreateTables();
            return true;
        }


    }
}
/*
 private void GetTableInfo()
{
DataRowView drvCols = null;
DataTable dtCols = null;
DataView dvColumns = null;
TableInfo tabInfoObj = null;
string sTableNm = null;
string sNamePrefix = null;
string sColName = null;
int nColType = 0;

// Get the list of tables that there are.
DataTable schemaTab =
m_oleDbSrcConn.GetOleDbSchemaTable(OleDbSchemaGuid .Tables,
new Object[] {null, null, null, "TABLE"});

for (int i = 0; i < schemaTab.Rows.Count; i++)
{
sTableNm = schemaTab.Rows[i].ItemArray[2].ToString();
sNamePrefix = sTableNm.Substring(0, 4);

if ((sTableNm != "_NEW_TABLES") && (sNamePrefix != "temp"))
{
tabInfoObj = new TableInfo(sTableNm);

// Get the column name and its type and sort them
// according to their position in the table.
dtCols = m_oleDbSrcConn.GetOleDbSchemaTable(
OleDbSchemaGuid.Columns,
new Object[]{null,null, sTableNm, null});
dvColumns = new DataView(dtCols);
dvColumns.Sort = "ORDINAL_POSITION";

// Get the column names and their types.
for (int j=0; j< dvColumns.Count; j++)
{
// Get the name of the column.
drvCols = dvColumns[j];
sColName = drvCols.Row.ItemArray[3].ToString();

// Get columns data type code and save it off.
nColType = Convert.ToInt32(drvCols.Row.ItemArray[11]);
tabInfoObj.AddColumn(sColName, nColType);
m_alTables.Add(tabInfoObj);
}
} // if

} // for
}


 */